SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

DECLARE @AlteredFlag BIT
SELECT @AlteredFlag	= 0;

IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.TABLES
			WHERE TABLE_SCHEMA = 'dbo'
				AND TABLE_NAME = 'Content'
				AND TABLE_TYPE = 'BASE TABLE' )
	-------------------------------------------------------------------------------
	--Table Already Exists - Execute ALTER Statements
	-------------------------------------------------------------------------------
	BEGIN
		
		IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Content' AND COLUMN_NAME = 'Url' )
		BEGIN
			ALTER TABLE Content ADD Url NVARCHAR(255) NOT NULL DEFAULT ''
			PRINT 'dbo.Content.Url column added.';
			SET @AlteredFlag = 1;
		END
		
		/*IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Content' AND COLUMN_NAME = 'Title' )
		BEGIN
			ALTER TABLE Content ALTER COLUMN Title NVARCHAR(255) NOT NULL
			PRINT 'dbo.Content.Title column Modified.';
			SET @AlteredFlag = 1;
		END
		
		IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Content' AND COLUMN_NAME = 'Description' )
		BEGIN
			ALTER TABLE Content ALTER COLUMN Description NVARCHAR(MAX) NOT NULL
			PRINT 'dbo.Content.Description column Modified.';
			SET @AlteredFlag = 1;
		END
		
		IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Content' AND COLUMN_NAME = 'Summery' )
		BEGIN
			ALTER TABLE Content ALTER COLUMN Summery NVARCHAR(1000) NULL
			PRINT 'dbo.Content.Summery column Modified.';
			SET @AlteredFlag = 1;
		END*/
		
		IF ( @AlteredFlag = 1 )
			BEGIN
				PRINT 'Table Content altered with latest changes.';		
				PRINT 'Table Content successfully upgraded.';
			END
		ELSE
			BEGIN
				PRINT 'Table Content requires no changes.';
			END
		END
ELSE
	-------------------------------------------------------------------------------
	--Table Does Not Exist - Create the Table
	-------------------------------------------------------------------------------
	BEGIN		
		CREATE TABLE [dbo].[Content](
			[ID] [int] IDENTITY(1,1) NOT NULL,
			[Title] [nvarchar](255) NOT NULL,
			[Description] [nvarchar](max) NOT NULL,
			[Summery] [nvarchar](1000) NULL,
			[ModifiedBy] [int] NOT NULL,
			[Created] [datetime] NOT NULL,
			[Modified] [datetime] NOT NULL,
			[Url] [nvarchar](255) NOT NULL,
		 CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED 
		(
			[ID] ASC
		)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
		) ON [PRIMARY]		

		ALTER TABLE [dbo].[Content]  WITH CHECK ADD  CONSTRAINT [FK_Content_User] FOREIGN KEY([ModifiedBy])
		REFERENCES [dbo].[User] ([ID])

		ALTER TABLE [dbo].[Content] CHECK CONSTRAINT [FK_Content_User]

		ALTER TABLE [dbo].[Content] ADD  DEFAULT ('') FOR [Url]	
	-------------------------------------------------------------------------------
	--	END OF CREATE STATEMENTS.
	--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
	-------------------------------------------------------------------------------
	-- Make sure object was created successfully.
	IF EXISTS (	SELECT *
				FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_SCHEMA = 'dbo'
					AND TABLE_NAME = 'Content'
					AND TABLE_TYPE = 'BASE TABLE' )


		BEGIN
			PRINT 'Table Content created successfully.';	
		END
	ELSE
		BEGIN
			PRINT 'ERROR: Failed to create Table Content.';
		END
	END
GO



